Copyright

DataDirect Connect ODBC for SQL Server Wire Protocol

© 2002 DataDirect Technologies. All rights reserved.

DataDirect, DataDirect Connect, and SequeLink are registered trademarks, and Client/Server MiddleWare, DataDirect Connect ADO, DataDirect Connect Integrator, DataDirect Connect JDBC, DataDirect Connect ODBC, DataDirect Connect OLE DB, DataDirect Connect Premium, DataDirect Reflector, and DataDirect SequeLink Integrator are trademarks of DataDirect Technologies. All other trademarks are the property of their respective owners.


About Connect ODBC for SQL Server
About Connect ODBC for SQL Server Wire Protocol

Connect ODBC for SQL Server Wire Protocol (the "SQL Server Wire Protocol driver") supports  Microsoft SQL Server 7.0 and SQL Server 2000 with Service Pack 1.

The SQL Server Wire Protocol driver is supported in the Windows and UNIX environments.

See the README file shipped with your DataDirect product for the file name of the SQL Server Wire Protocol driver.


SQL Server Wire Protocol Driver Requirements

For support of version 7.0 of SQL Server, the driver requires the SQL Server 7.0 versions of Net-Library DLL files, which are installed when you install Connect ODBC for SQL Server. The driver communicates with network software through the SQL Server Net-Library interface.

There are no client requirements for the SQL Server Wire Protocol driver.


Configuring Data Sources

Data sources are configured and modified through the ODBC Administrator.To configure a SQL Server Wire Protocol data source:

1	Start the ODBC Administrator to display a list of data sources.

2	If you are configuring a new data source, click Add.  A list of installed drivers appears. Select SQL Server and click Finish to display the ODBC SQL Server Wire Protocol Driver Setup dialog box.

If you are configuring an existing data source, select the data source name and click Configure to display the ODBC SQL Server Wire Protocol Driver Setup dialog box.

3	Specify a data source name, a server name, a database name and optionally, a description. Click Apply.

4	Click the Advanced tab to configure optional data source settings. Click Apply.

5	Click Translate to display the Select Translator dialog box, which lists the translators specified in the ODBC Translators section of the system information. DataDirect provides a translator named “OEM to ANSI” that translates your data from the IBM PC character set to the ANSI character set. Click OK to close this dialog box and perform the translation.

6	Click Test Connect to attempt to connect to the data source using the connection properties specified in the Driver Setup dialog box.

7	Click OK or Cancel. If you click OK, the values you have specified become the defaults when you connect to the data source. You can change these defaults by using this procedure to reconfigure your data source. You can override these defaults by connecting to the data source using a connection string with alternate values.


Test Connection

At any point during the configuration process, you can click Test Connect to attempt to connect to the data source using the connection properties specified in the Driver Setup dialog box. A Logon dialog box is displayed. After providing connection information, click OK.

•	If the driver can connect, it releases the connection and displays a "connection established" message. Click OK.

•	If the driver cannot connect because of an improper environment or incorrect connection value, it will display an appropriate error message.

Verify that the all required client software is properly installed. If it is not, you will see the message:

Specified driver could not be loaded due to system error [xxx].

Click OK.


Connecting to a Data Source Using a Logon Dialog
Connecting to a Data Source Using a Logon Dialog Box

Some ODBC applications display a Logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified.

The SQL Server Wire Protocol driver displays the SQL Server Wire Protocol Login dialog box when you call an ODBC connection without specifying enough information for the driver to connect to a SQL Server database.

In the Logon dialog box, perform the following steps:

1	Select the Use Trusted Connection check box to specify that the SQL Server Wire Protocol driver request a secure (or trusted) connection to a SQL Server running on Windows NT. SQL Server uses integrated login security to establish connections using this data source, regardless of the current login security mode at the server. Any login ID or password supplied is ignored. The SQL Server system administrator must have associated your Windows network ID with a SQL Server login ID.

Clear this box to specify that SQL Server uses standard login security to establish connections using this data source. You must specify a login ID and password for all connection requests.

2	Type the SQL Server login ID to use for the connection if Use Trusted Connection is not selected. If Use Trusted Connection is selected, the Login ID field is disabled.

3	Type the password to use for the connection if Use Trusted Connection is not selected. If Use Trusted Connection is selected, the Password field is disabled.

4	You can click Options to display the SQL Server Logon Options pane and specify the initial SQL Server database to which to connect.

Database: Type the default database to use on the connection. This overrides the default database specified for the login on the server. If no database is specified, the connection uses the default database specified for the login on the server.

Language: Type the national language to use for SQL Server system messages. The SQL Server must have the language installed. This overrides the default language specified for the login on the server. If no language is specified, the connection uses the default language specified for the login on the server.

Application Name: Optionally, you can type the application name to be stored in the program_name column in the row for this connection in master.dbo.sysprocesses.

Workstation ID: Optionally, you can type the workstation ID to be stored in the hostname column in the row for this connection in master.dbo.sysprocesses.

5	Click OK to log on to the SQL Server database installed on the server you specified and to update the values in the system information.


Connecting to Data Source Using a Connection String
Connecting to a Data Source Using a Connection String

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in system information. These values are not written to the system information.

The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for SQL Server is:

DSN=Accounting;UID=JOHN;PWD=XYZZY

The paragraphs that follow give the long and short names, when applicable, for each attribute, as well as a description. The defaults listed are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is the default.

Address: The network address of the server running SQL Server. Used only if the Server keyword does not specify the network name of a server running SQL Server. Address is usually the network name of the server, but can be other names such as a pipe, or a TCP/IP port and socket address. For example, on TCP/IP: 199.199.199.5, 1433 or MYSVR, 1433.

AnsiNPW: AnsiNPW={yes | no}. Determines whether ANSI-defined behaviors are exposed.

When set to yes, the driver uses ANSI-defined behaviors for handling NULL comparisons, character data padding, warnings, and NULL concatenation.

When set to no, ANSI-defined behaviors are not exposed.

APP: The name of the application calling SQLDriverConnect (optional). If specified, this value is stored in the master.dbo.sysprocesses column program_name and is returned by sp_who and the Transact-SQL APP_NAME function.

AttachDBFileName: The name of the primary file of an attachable database. Include the full path and escape any slash ( \ ) characters if using a C character string variable:

AttachDBFileName=c:\\MyFolder\\MyDB.mdf

This database is attached and becomes the default database for the connection. To use AttachDBFileName you must also specify the database name in either the SQLDriverConnnect DATABASE parameter or the SQL_COPT_CURRENT_CATALOG connection attribute. If the database was previously attached, SQL Server will not reattach it; it will use the attached database as the default for the connection.

AutoTranslate: AutoTranslate={yes | no}. Determines how ANSI character strings are translated.

When set to yes, ANSI character strings sent between the client and server are translated by converting through Unicode to minimize problems in matching extended characters between the code pages on the client and the server.

These conversions are performed on the client by the SQL Server Wire Protocol driver. This requires that the same ANSI code page (ACP) used on the server be available on the client.

These settings have no effect on the conversions that occur for the following transfers:

Unicode SQL_C_WCHAR client data sent to char, varchar, or text on the server.

Char, varchar, or text server data sent to a Unicode SQL_C_WCHAR variable on the client.

ANSI SQL_C_CHAR client data sent to Unicode nchar, nvarchar, or ntext on the server.

Unicode char, varchar, or text server data sent to an ANSI SQL_C_CHAR variable on the client.

When set to no, character translation is not performed.

The SQL Server Wire Protocol driver does not translate client ANSI character SQL_C_CHAR data sent to char, varchar, or text variables, parameters, or columns on the server. No translation is performed on char, varchar, or text data sent from the server to SQL_C_CHAR variables on the client.

If the client and SQL Server are using different ACPs, then extended characters can be misinterpreted.

DATABASE: The name of the default SQL Server database for the connection. If DATABASE is not specified, the default database defined for the login is used. The default database from the ODBC data source overrides the default database defined for the login. The database must be an existing database unless AttachDBFileName is also specified. If AttachDBFileName is specified, the primary file it points to is attached and given the database name specified by DATABASE.

LANGUAGE: The SQL Server language name (optional). SQL Server can store messages for multiple languages in sysmessages. If connecting to a SQL Server with multiple languages, This attribute specifies which set of messages are used for the connection.

Network: The name of a network library dynamic-link library. The name need not include the path and must not include the .dll file name extension, for example, Network=dbnmpntw.

PWD: The password for the SQL Server login account specified in the UID parameter. PWD need not be specified if the login has a NULL password or when using Windows NT authentication (Trusted_Connection=yes).

QueryLogFile: The full path and file name of a file to be used for logging data about long-running queries.

QueryLog_On: QueryLog_On={yes | no}. Determines whether long-running query data is logged.

When set to yes, logging long-running query data is enabled on the connection.

When set to no, long-running query data is not logged.

QueryLogTime: A digit character string specifying the threshold (in milliseconds) for logging long-running queries. Any query that does not receive a response in the time specified is written to the long-running query log file.

QuotedID: QuotedID={yes | no}. Determines whether QUOTED_IDENTIFIERS is set ON or OFF for the connection.

When set to yes, QUOTED_IDENTIFIERS is set ON for the connection, and SQL Server uses the SQL-92 rules regarding the use of quotation marks in SQL statements.

When set to no, QUOTED_IDENTIFIERS is set OFF for the connection, and SQL Server uses the legacy Transact-SQL rules regarding the use of quotation marks in SQL statements.

Regional: Regional={yes | no}. Determines how currency, date, and time data are converted.

When set to yes, the SQL Server Wire Protocol driver uses client settings when converting currency, date, and time data to character data. The conversion is one way only; the driver does not recognize non-ODBC standard formats for date strings or currency values.

When set to no, the driver uses ODBC standard strings to represent currency, date, and time data that is converted to string data.

SAVEFILE: The name of an ODBC data source file into which the attributes of the current connection are saved if the connection is successful.

SERVER: The name of a server running SQL Server on the network. The value must be either the name of a server on the network, or the name of a SQL Server Client Network Utility advanced server entry. You can enter "(local)" as the server name on Windows NT to connect to a copy of SQL Server running on the same computer.

StatsLogFile: The full path and file name of a file used to record SQL Server Wire Protocol driver performance statistics.

StatsLog_On: StatsLog_On={yes | no}. Determines whether SQL Server Wire Protocol driver performance data is available.

When set to yes, SQL Server Wire Protocol driver performance data is captured.

When set to no, SQL Server Wire Protocol driver performance data is not available on the connection.

Trusted_Connection: Trusted_Connection={yes | no}. Determines what information the SQL Server Wire Protocol driver will use for login validation.

When set to yes, the SQL Server Wire Protocol driver uses Windows NT Authentication Mode for login validation. The UID and PWD keywords are optional.

When set to no, the SQL Server Wire Protocol driver uses a SQL Server username and password for login validation. The UID and PWD keywords must be specified.

UID: A valid SQL Server login account. UID need not be specified when using Windows NT authentication.

WSID: The workstation ID. Typically, this is the network name of the computer on which the application resides (optional). If specified, this value is stored in the master.dbo.sysprocesses column hostname and is returned by sp_who and the Transact-SQL HOST_NAME function.


Unicode Support

The SQL Server Wire Protocol driver maps the SQL Server data types as follows:

SQL Server Data Type	Mapped to…
Nchar	SQL_WCHAR
Ntext	SQL_WLONGVARCHAR
Sysname	SQL_WVARCHAR


Data Types

The SQL Server data types are mapped to the standard ODBC data types as follows:

SQL Server	ODBC Data Type
Binary	SQL_BINARY
Bit	SQL_BIT
Char	SQL_CHAR
Datetime	SQL_TYPE_TIMESTAMP
Decimal	SQL_DECIMAL
Decimal() identity	SQL_DECIMAL
Float	SQL_FLOAT
Image	SQL_LONGVARBINARY
Int	SQL_INTEGER
Int identity	SQL_INTEGER
Money	SQL_DECIMAL
Nchar	SQL_WCHAR
Ntext*	SQL_WLONGVARCHAR
Numeric	SQL_NUMERIC
Numeric() identity	SQL_NUMERIC
Nvarchar	SQL_WVARCHAR
Real	SQL_REAL
Smalldatetime	SQL_TYPE_TIMESTAMP
Smallint	SQL_SMALLINT
Smallint identity	SQL_SMALLINT
Smallmoney	SQL_DECIMAL
Sysname	SQL_VARCHAR
Sysname	SQL_WVARCHAR
Text	SQL_LONGVARCHAR
Timestamp	SQL_VARBINARY
Tinyint	SQL_TINYINT
Tinyinit identity	SQL_TINYINT
Uniqueidentifier	SQL_GUID
Varbinary	SQL_VARBINARY
Varchar	SQL_VARCHAR


Isolation and Lock Levels Supported

SQL Server supports isolation levels 0 (read uncommitted), 1 (read committed), 2 (repeatable read), and 3 (serializable). SQL Server supports row- and table-level locking.


ODBC Conformance Level

The SQL Server Wire Protocol driver supports ODBC conformance level 2.


Number of Connections and Statements Supported

The SQL Server database system supports multiple connections. With two-phased commit, SQL Server supports multiple statements per connection. Otherwise, SQL Server supports a single statement per connection if SQL_AUTOCOMMIT is 0 and multiple statements per connection if SQL_AUTOCOMMIT is 1.


General Tab, ODBC SQL Server Wire Protocol Driver Setup

Use the ODBC SQL Server Wire Protocol Driver Setup dialog box to create new SQL Server data sources or configure existing data sources.

Data Source Name: Type a string that identifies this SQL Server Wire Protocol data source configuration in the system information. Examples include "Accounting" or "SQL Server-Serv1."

Description: Type an optional long description of a data source name. For example, "My Accounting Database" or "SQL Server on Server number 1."

Server Name: Type the name of the server that contains the database you want.

Database Name: Type the name of the database to which you want to connect by default. If you do not specify a value, the default database defined by SQL Server is used.

Advanced Tab

Displays the Advanced tab on the ODBC SQL Server Wire Protocol Driver Setup dialog box, where you can configure optional data source settings.

OK

Cancel

Apply

Test Connect


Advanced Tab, ODBC SQL Server Wire Protocol Driver Setup

Use the Advanced tab on the ODBC SQL Server Wire Protocol Driver Setup dialog box to specify optional settings when you create new SQL Server data sources or configure existing data sources.

Language: Type the national language to be used by the client. The default is English.

Application Name: Type the name SQL Server uses to identify your application.

Workstation ID: Type the workstation ID used by the client.

Default Logon ID: Type the default logon ID used to connect to your SQL Server database. This ID is case-sensitive. A logon ID is required only if security is enabled on your database. Your ODBC application may override this value or you may override this value in the Logon dialog box or connection string.

Use NT Authentication: Select this check box to enable Windows NT security. When enabled, the Default Logon ID field is inactive because Windows NT security passes the logon ID and password. Selecting this check box also activates the corresponding check box on the Logon Dialog.

Enable Quoted Identifiers: Select this check box to enable quoted identifiers; that is, identifiers in SQL Server can be quoted using a quoting character. The default is not selected.

Translate Button

Displays the Select Translator, where you can translate your data from one character set to another. Choose the OEM to ANSI translator to translate your data from the IBM PC character set to the ANSI character set.

OK

Cancel

Apply

Test Connect


Apply Button

Writes the settings you have specified to the system information. These settings remain in effect until you change them in this dialog box. Clicking Cancel does not affect settings that have been applied.


OK Button

Writes the settings you have specified to the system information and closes the dialog box.


Cancel Button

Closes the dialog box without saving settings that have not been applied.


Contacting Technical Support

DataDirect Technologies provides technical support for all registered users of this product, including limited installation support, for the first 30 days. You can register online for your SupportLink user ID and password, which provide access to the password-protected areas of the SupportLink web site, at http://support.datadirect-technologies.com. Select Register on the left toolbar, complete the information on the Register page, and click Submit. Your user ID and password are issued to you by email upon registration.

For support after 30 days, contact us using one of the following methods or purchase further support by enrolling in the SupportLink program. For more information about SupportLink, contact your sales representative.

The DataDirect Technologies web site provides the latest support information through SupportLink Online, our global service network that provides access to support contact details, tools, and valuable information. Our SupportLink users access information using the web and automatic email notification. SupportLink Online includes a knowledge base that allows you to search on keywords for technical bulletins and other information. You also can download product fixes and upgrades for your DataDirect products.

World Wide Web

http://support.datadirect-technologies.com

E-Mail

USA, Canada, Mexico, Australia, and Asia Pacific (except Japan)	datadirect.answerline@datadirect-technologies.com
Japan	jpn.answerline@datadirect.co.jp (beginning March 2002) dd.answerline@merant.co.jp (until March 2002)
All other countries	int.datadirect.answerline@datadirect-technologies.com
Local Telephone Support

Local phone numbers can be found at:

http://support.datadirect-technologies.com/websupport/supportnet/datadirect/
supportnetanswerline.asp

NOTE: You must have a SupportLink user ID and password to access this web site. You can register online for your SupportLink user ID and password at http://support.datadirect-technologies.com. Select Register on the left toolbar, complete the information on the Register page, and click Submit. Your user ID and password are issued immediately and confirmation is sent to you by email.

Answerline support is available 24 hours a day, seven days a week.

Fax Information

Fax US	1 919 461 4527
Fax Int.	+32 (0) 15 32 09 19

Information to Provide

When you contact us, please include this information:

•	The product serial number or a case number. If you do not have a SupportLink contract, we will ask you to speak with a sales representative.

•	Your name and organization. For a first-time call, you may be asked for full customer information, including location and contact details.

•	The version number of your DataDirect product.

•	The type and version of your operating system.

•	Any third-party software or other environment information required to understand the problem.

•	A brief description of the problem, including any error messages that you have received, and the steps preceding the occurrence of the problem. Depending on the complexity of the problem, you may be asked to submit an example so that we can recreate the problem.

•	An assessment of the severity level of the problem.


Windows

•	Windows 98

•	Windows Me

•	Windows NT with Service Pack 5 and higher

•	Windows 2000 with Service Pack 1 and higher

•	Windows XP Home Edition and Professional


UNIX

•	AIX 4.3.3 and higher with the 5.0.2.0 C++ runtime libraries

•	HP-UX 11 and 11i

•	Sun Solaris 7 and 8

•	Linux (Red Hat Linux 6.2, 7.1, and 7.2; Caldera OpenLinux 2.3 and 3.1; SuSE Linux 6.4 and 7.3)